﻿using BiliBiliiMG.Model;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using Maticsoft.DBUtility;

namespace BilibiliDAL
{
    public class LeaveCommentsDal
    {
        public bool Exists(int UnitID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from LeaveComments");
            strSql.Append(" where ");
            strSql.Append(" UnitID = @UnitID  ");
            SqlParameter[] parameters = {
                new SqlParameter("@UnitID", SqlDbType.Int,4)
            };
            parameters[0].Value = UnitID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }



        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(LeaveCommentsModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into LeaveComments(");
            strSql.Append("PUnitID,IPAddress,Content,IsDelect,IsDisplay,CreateTime,Name,ReviseTime");
            strSql.Append(") values (");
            strSql.Append("@PUnitID,@IPAddress,@Content,@IsDelect,@IsDisplay,@CreateTime,@Name,@ReviseTime");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                new SqlParameter("@PUnitID", SqlDbType.Int,4) ,
                new SqlParameter("@IPAddress", SqlDbType.NVarChar,50) ,
                new SqlParameter("@Content", SqlDbType.NVarChar,-1) ,
                new SqlParameter("@IsDelect", SqlDbType.Int,4) ,
                new SqlParameter("@IsDisplay", SqlDbType.Int,4) ,
                new SqlParameter("@CreateTime", SqlDbType.DateTime,3) ,
                new SqlParameter("@Name", SqlDbType.NVarChar,50) ,
                new SqlParameter("@ReviseTime", SqlDbType.DateTime,3)

            };

            parameters[0].Value = model.PUnitID;
            parameters[1].Value = model.IPAddress;
            parameters[2].Value = model.Content;
            parameters[3].Value = model.IsDelect;
            parameters[4].Value = model.IsDisplay;
            parameters[5].Value = model.CreateTime;
            parameters[6].Value = model.Name;
            parameters[7].Value = model.ReviseTime;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {

                return Convert.ToInt32(obj);

            }

        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(LeaveCommentsModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update LeaveComments set ");

            strSql.Append(" PUnitID = @PUnitID , ");
            strSql.Append(" IPAddress = @IPAddress , ");
            strSql.Append(" Content = @Content , ");
            strSql.Append(" IsDelect = @IsDelect , ");
            strSql.Append(" IsDisplay = @IsDisplay , ");
            strSql.Append(" CreateTime = @CreateTime , ");
            strSql.Append(" Name = @Name , ");
            strSql.Append(" ReviseTime = @ReviseTime  ");
            strSql.Append(" where UnitID=@UnitID ");

            SqlParameter[] parameters = {
                new SqlParameter("@UnitID", SqlDbType.Int,4) ,
                new SqlParameter("@PUnitID", SqlDbType.Int,4) ,
                new SqlParameter("@IPAddress", SqlDbType.NVarChar,50) ,
                new SqlParameter("@Content", SqlDbType.NVarChar,-1) ,
                new SqlParameter("@IsDelect", SqlDbType.Int,4) ,
                new SqlParameter("@IsDisplay", SqlDbType.Int,4) ,
                new SqlParameter("@CreateTime", SqlDbType.DateTime,3) ,
                new SqlParameter("@Name", SqlDbType.NVarChar,50) ,
                new SqlParameter("@ReviseTime", SqlDbType.DateTime,3)

            };

            parameters[0].Value = model.UnitID;
            parameters[1].Value = model.PUnitID;
            parameters[2].Value = model.IPAddress;
            parameters[3].Value = model.Content;
            parameters[4].Value = model.IsDelect;
            parameters[5].Value = model.IsDisplay;
            parameters[6].Value = model.CreateTime;
            parameters[7].Value = model.Name;
            parameters[8].Value = model.ReviseTime;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int UnitID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from LeaveComments ");
            strSql.Append(" where UnitID=@UnitID");
            SqlParameter[] parameters = {
                new SqlParameter("@UnitID", SqlDbType.Int,4)
            };
            parameters[0].Value = UnitID;


            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool DeleteList(string UnitIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from LeaveComments ");
            strSql.Append(" where ID in (" + UnitIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public LeaveCommentsModel GetModel(int UnitID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select UnitID, PUnitID, IPAddress, Content, IsDelect, IsDisplay, CreateTime, Name, ReviseTime  ");
            strSql.Append("  from LeaveComments ");
            strSql.Append(" where UnitID=@UnitID");
            SqlParameter[] parameters = {
                new SqlParameter("@UnitID", SqlDbType.Int,4)
            };
            parameters[0].Value = UnitID;


            LeaveCommentsModel model = new LeaveCommentsModel();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["UnitID"].ToString() != "")
                {
                    model.UnitID = int.Parse(ds.Tables[0].Rows[0]["UnitID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PUnitID"].ToString() != "")
                {
                    model.PUnitID = int.Parse(ds.Tables[0].Rows[0]["PUnitID"].ToString());
                }
                model.IPAddress = ds.Tables[0].Rows[0]["IPAddress"].ToString();
                model.Content = ds.Tables[0].Rows[0]["Content"].ToString();
                if (ds.Tables[0].Rows[0]["IsDelect"].ToString() != "")
                {
                    model.IsDelect = int.Parse(ds.Tables[0].Rows[0]["IsDelect"].ToString());
                }
                if (ds.Tables[0].Rows[0]["IsDisplay"].ToString() != "")
                {
                    model.IsDisplay = int.Parse(ds.Tables[0].Rows[0]["IsDisplay"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(ds.Tables[0].Rows[0]["CreateTime"].ToString());
                }
                model.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                if (ds.Tables[0].Rows[0]["ReviseTime"].ToString() != "")
                {
                    model.ReviseTime = DateTime.Parse(ds.Tables[0].Rows[0]["ReviseTime"].ToString());
                }

                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM LeaveComments ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM LeaveComments ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }


    }
}